package com.iflytek.application.common.springdata.jdbc.repository.support;


import com.iflytek.application.common.springdata.jdbc.repository.JdbcTemplateRepository;
import com.iflytek.application.common.springjdbc.Pagination;
import com.iflytek.application.common.springdata.jdbc.utils.SuperBeanUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/***
 * Created by guanxf on 2015/12/16.
 */
public class MysqlJdbcTemplateRepository implements JdbcTemplateRepository {
	private  JdbcTemplate jdbcTemplate;
	public MysqlJdbcTemplateRepository(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	public int save(String tablename, Object object) throws Exception {
		Class<?> c = object.getClass();
		Field[] fields = c.getDeclaredFields();
		String fieldAy[] = new String[fields.length - 1];
		final String valuesAy[] = new String[fields.length - 1];
		int j = 0;
		for (int i = 0; i < fields.length; i++) {
			String fname = fields[i].getName();
			if (!"id".equals(fname)) {
				PropertyDescriptor pd = new PropertyDescriptor(fname, c);
				Method getMethod = pd.getReadMethod();//获得get方法
				Object o = getMethod.invoke(object);//执行get方法返回一个Object
				fieldAy[j] = fname;
				if (o != null) {
					valuesAy[j] = o.toString();
				} else {
					valuesAy[j] = "";
				}
				j++;
			}
		}
		PropertyDescriptor pd = new PropertyDescriptor("id", c);
		Method getMethod = pd.getReadMethod();//获得get方法
		Object o = getMethod.invoke(object);//执行get方法返回一个Object
		int id = Integer.parseInt(o.toString());
		if (id > 0) {
			String dv = "";
			for (int i = 0; i < fieldAy.length; i++) {
				if (i != fieldAy.length - 1) {
					dv += fieldAy[i] + "='" + valuesAy[i] + "',";
				} else {
					dv += fieldAy[i] + "='" + valuesAy[i] + "'";
				}
			}
			String updateSql = "update " + tablename + " set  " + dv + " where id=" + id;
			System.out.println(updateSql);
			this.jdbcTemplate.execute(updateSql);
			return id;
		} else {
			String fstr = "";
			String v = "";
			for (int i = 0; i < fieldAy.length; i++) {
				{
					if (i != fieldAy.length - 1) {
						fstr += fieldAy[i] + ",";
						v += "?,";
					} else {
						fstr += fieldAy[i];
						v += "?";
					}
				}
			}
			final String sql = "insert into " + tablename + "(" + fstr + ")"
					+ " values(" + v + ")";
			System.out.println(sql);
			KeyHolder keyHolder = new GeneratedKeyHolder();
			this.jdbcTemplate.update(new PreparedStatementCreator() {

				public PreparedStatement createPreparedStatement(Connection con)
						throws SQLException {
					PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

					for (int i = 0; i < valuesAy.length; i++) {
						ps.setString(i + 1, valuesAy[i]);
					}
					return ps;
				}

			}, keyHolder);
			// 获得 插入 id
			return keyHolder.getKey().intValue();
		}
	}
 
	public int update(String tablename, String keyid, Object object) throws Exception {
		Class<?> c = object.getClass();
		Field[] fields = c.getDeclaredFields();
		//String fieldsStr="(";
		//String valuesStr="(";
		String fieldAy[] = new String[fields.length - 1];
		final String valuesAy[] = new String[fields.length - 1];

		int j = 0;
		for (int i = 0; i < fields.length; i++) {
			String fname = fields[i].getName();
			if (!keyid.equals(fname)) {
				PropertyDescriptor pd = new PropertyDescriptor(fname, c);
				Method getMethod = pd.getReadMethod();//获得get方法
				Object o = getMethod.invoke(object);//执行get方法返回一个Object
				fieldAy[j] = fname;
				if (o != null) {
					valuesAy[j] = o.toString();
				} else {
					valuesAy[j] = "";
				}
				j++;
			}
		}


		PropertyDescriptor pd = new PropertyDescriptor(keyid, c);
		Method getMethod = pd.getReadMethod();//获得get方法
		Object o = getMethod.invoke(object);//执行get方法返回一个Object
		int id = Integer.parseInt(o.toString());
		if (id > 0) {
			String dv = "";
			for (int i = 0; i < fieldAy.length; i++) {
				if (i != fieldAy.length - 1) {
					dv += fieldAy[i] + "='" + valuesAy[i] + "',";
				} else {
					dv += fieldAy[i] + "='" + valuesAy[i] + "'";
				}
			}
			String updateSql = "update " + tablename + " set  " + dv + " where " + keyid + "=" + id;
			System.out.println(updateSql);
			this.jdbcTemplate.execute(updateSql);
			return id;
		}
		return id;
	}
	// 批量插入,  
    public List<?> saveOrUpdateAll(String tablename,final String keyid,Object object,final List<?> list) throws Exception {
		final Class<?> c = object.getClass();
		final Field[] fields = c.getDeclaredFields();
		final String fieldAy[] = new String[fields.length - 1];
		int j = 0;
		for (int i = 0; i < fields.length; i++) {
			String fname = fields[i].getName();
			if (!keyid.equals(fname)) {
				fieldAy[j] = fname;
				j++;
			}
		}
		String fstr = "";
		String v = "";
		for (int i = 0; i < fieldAy.length; i++) {
			{
				if (i != fieldAy.length - 1) {
					fstr += fieldAy[i] + ",";
					v += "?,";
				} else {
					fstr += fieldAy[i];
					v += "?";
				}

			}

		}

		final String sql = "insert into " + tablename + "(" + fstr + ")"
				+ " values(" + v + ")";

		this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int in)
					throws SQLException {
				String valuesAy[] = new String[fields.length - 1];
				int j = 0;
				for (int i = 0; i < fields.length; i++) {
					String fname = fields[i].getName();
					if (!keyid.equals(fname)) {
						PropertyDescriptor pd = null;
						try {
							pd = new PropertyDescriptor(fname, c);
						} catch (IntrospectionException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						Method getMethod = pd.getReadMethod();//获得get方法
						Object o = null;
						try {
							o = getMethod.invoke(list.get(in));
						} catch (Exception e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						//执行get方法返回一个Object
						fieldAy[j] = fname;
						if (o != null) {
							valuesAy[j] = o.toString();
						} else {
							valuesAy[j] = "";
						}
						j++;
					}
				}
				for (int k = 0; k < valuesAy.length; k++) {
					ps.setString(k + 1, valuesAy[k]);
				}
			}

			@Override
			public int getBatchSize() {
				return list.size();
			}
		});
		//哪个方法可以获得list中每个test对象的id?我不想重新进行查询
		return list;
		
    }


	public Pagination queryAll(Class<?> c, String tablename, String where, String order, int pCurrentPage, int pPageSize)  {
		 Pagination pagination = new Pagination();
				try {
					String  countSql="select count(*) from "+tablename+" "+(where.length()>0?"where":"")+" "+where;
					String orderSql=order.length()>0?" order by "+order:"";
					int firstRow = (pCurrentPage - 1) * pPageSize ;		
					String executeSql ="select * from "+tablename+" "+(where.length()>0?"where":"")+" "+where +orderSql+" limit " + firstRow + " , " + pPageSize;					
					int mRowCount = this.jdbcTemplate.queryForObject(countSql, Integer.class);
					if(mRowCount>0){
						List<Map<String, Object>> list = this.jdbcTemplate.queryForList(executeSql);
						List<Object> returnList=new ArrayList<Object>();
						for(int i=0;i<list.size();i++)
						{
							Map<String, Object> map = list.get(i);
							Object object=SuperBeanUtils.convertForObject(c, map);
							returnList.add(object);
						}
						pagination.setContent(returnList);
					}
					pagination.setSize(pPageSize);
					pagination.setNumber(pCurrentPage);
					pagination.setTotalElements(mRowCount);
				} catch (Exception e) {
					e.printStackTrace();
				} 			
		return pagination;
	}

	@Override
	public Object findOne(Class<?> c, String table, int id)
	{
		Object obj=null;
		try {
		if(id==0) return obj;
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList("select * from "+table+" where id="+id);
		Map<String, Object> map = list.get(0);
			Object object=SuperBeanUtils.convertForObject(c, map);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return obj;
	}


	@Override
	public Object findOne(Class<?> c,  String sql) throws Exception {
		Object obj= this.jdbcTemplate.queryForObject(sql,c);
		return obj;
	}

	@Override
	public Object findAll(Class<?> c,String sql) throws Exception {
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
		List<Object> returnList=new ArrayList<Object>();
		for(int i=0;i<list.size();i++)
		{
			Map<String, Object> map = list.get(i);
			Object object=SuperBeanUtils.convertForObject(c, map);
			returnList.add(object);
		}
		return returnList;
	}

	@Override
	public Object findAll(Class<?> c, String sql, Object[] params) throws DataAccessException {
		return this.jdbcTemplate.queryForList(sql,params,c);
	}
}
